package com.qingmu.club.minaapi.custom.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.qingmu.club.minaapi.plus.entity.ClubUsers;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 会员关注门店表 Mapper 接口
 * </p>
 *
 * @author duke
 * @since 2019-08-28
 */
public interface CustomClubUsersMapper extends BaseMapper<ClubUsers> {

    //门店粉丝
    @Select("SELECT COUNT(*) fansSum,a.shop_name shopName,a.shop_avater avater FROM club_users a WHERE   a.attention_status = 1  AND  a.club_id=#{params.club_id} GROUP BY a.shop_name ORDER BY fansSum DESC")
    List<Map<String, Object>> sumShopRankingList(@Param("params") Map<String, Object> params);

    @Select("SELECT COUNT(*) fansSum,a.shop_name shopName,a.shop_avater avater FROM club_users a WHERE   a.attention_status = 1  AND  a.club_id=#{params.club_id} AND date_format(a.create_time,'%Y')=#{params.create_time} GROUP BY a.shop_name ORDER BY fansSum DESC")
    List<Map<String, Object>> yearShopRankingList(@Param("params") Map<String, Object> params);

    @Select("SELECT COUNT(*) fansSum,a.shop_name shopName,a.shop_avater avater FROM club_users a WHERE   a.attention_status = 1 AND  a.club_id=#{params.club_id} AND a.create_time BETWEEN  #{params.startDate} AND  #{params.endDate} GROUP BY a.shop_name ORDER BY fansSum DESC")
    List<Map<String, Object>> quarterShopRankingList(@Param("params") Map<String, Object> params);

    @Select("SELECT COUNT(*) fansSum,a.shop_name shopName,a.shop_avater avater FROM club_users a WHERE   a.attention_status = 1  AND   a.club_id=#{params.club_id} AND date_format(a.create_time,'%Y-%m')=#{params.create_time} GROUP BY a.shop_name ORDER BY fansSum DESC")
    List<Map<String, Object>> monthShopRankingList(@Param("params") Map<String, Object> params);

    @Select("SELECT COUNT(*) fansSum,a.shop_name shopName,a.shop_avater avater FROM club_users a WHERE   a.attention_status = 1  AND  a.club_id=#{params.club_id} AND a.create_time BETWEEN  #{params.startDate} AND  #{params.endDate} GROUP BY a.shop_name ORDER BY fansSum DESC")
    List<Map<String, Object>> weekShopRankingList(@Param("params") Map<String, Object> params);


    //个人粉丝
    @Select("SELECT COUNT(*) fansSum,a.affiliation_coach_name coachName,a.affiliation_coach_avater avater FROM club_users a WHERE a.attention_type=0 AND a.attention_status = 1  AND   a.club_id=#{params.club_id} GROUP BY coachName ORDER BY fansSum DESC")
    List<Map<String, Object>> sumPersonRankingList(@Param("params") Map<String, Object> params);

    @Select("SELECT COUNT(*) fansSum,a.affiliation_coach_name coachName,a.affiliation_coach_avater avater FROM club_users a WHERE a.attention_type=0 AND a.attention_status = 1  AND   a.club_id=#{params.club_id} AND date_format(a.create_time,'%Y')=#{params.create_time} GROUP BY coachName ORDER BY fansSum DESC")
    List<Map<String, Object>> yearPersonRankingList(@Param("params") Map<String, Object> params);

    @Select("SELECT COUNT(*) fansSum,a.affiliation_coach_name coachName,a.affiliation_coach_avater avater FROM club_users a WHERE a.attention_type=0 AND a.attention_status = 1  AND   a.club_id=#{params.club_id} AND a.create_time BETWEEN  #{params.startDate} AND  #{params.endDate} GROUP BY coachName ORDER BY fansSum DESC")
    List<Map<String, Object>> quarterPersonRankingList(@Param("params") Map<String, Object> params);

    @Select("SELECT COUNT(*) fansSum,a.affiliation_coach_name coachName,a.affiliation_coach_avater avater FROM club_users a WHERE a.attention_type=0 AND a.attention_status = 1  AND   a.club_id=#{params.club_id} AND date_format(a.create_time,'%Y-%m')=#{params.create_time} GROUP BY coachName ORDER BY fansSum DESC")
    List<Map<String, Object>> monthPersonRankingList(@Param("params") Map<String, Object> params);

    @Select("SELECT COUNT(*) fansSum,a.affiliation_coach_name coachName,a.affiliation_coach_avater avater FROM club_users a WHERE a.attention_type=0 AND a.attention_status = 1  AND  a.club_id=#{params.club_id} AND a.create_time BETWEEN  #{params.startDate} AND  #{params.endDate} GROUP BY coachName ORDER BY fansSum DESC")
    List<Map<String, Object>> weekPersonRankingList(@Param("params") Map<String, Object> params);


}
